package nxu.it.movieapi.service.pg.dao;

import nxu.it.api.model.Movie;
import nxu.it.api.model.SimpleStaff;
import nxu.it.api.model.Staff;
import org.jdbi.v3.sqlobject.config.RegisterBeanMapper;
import org.jdbi.v3.sqlobject.customizer.Bind;
import org.jdbi.v3.sqlobject.customizer.Define;
import org.jdbi.v3.sqlobject.statement.SqlQuery;

import java.util.List;
import java.util.Optional;

public interface StaffPgDao {
    @SqlQuery("SELECT * FROM staff WHERE id = :staffId")
    @RegisterBeanMapper(Staff.class)
    Optional<Staff> findById(@Bind("staffId") Integer staffId);

    @SqlQuery("SELECT * FROM staff " +
            "<where> " +
            "AND (english_name LIKE '%' || :englishName || '%' OR :englishName IS NULL) " +
            "AND (chinese_name LIKE '%' || :chineseName || '%' OR :chineseName IS NULL) " +
            "AND (region = :region OR :region IS NULL) " +
            "AND (gender = :gender OR :gender IS NULL) " +
            "ORDER BY <orderBy> " +
            "LIMIT :size OFFSET :offset")
    @RegisterBeanMapper(SimpleStaff.class)
    List<SimpleStaff> complexPage(
            @Define("where") String whereClause,
            @Define("orderBy") String orderByClause,
            @Bind("englishName") String englishName,
            @Bind("chineseName") String chineseName,
            @Bind("region") String region,
            @Bind("gender") String gender,
            @Bind("size") int size,
            @Bind("offset") int offset
    );

    @SqlQuery("SELECT COUNT(*) FROM staff " +
            "<where>")
    int complexCount(
            @Define("where") String whereClause
    );
}
